USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetAccountsXml')
	DROP FUNCTION dbo.GetAccountsXml
GO


CREATE FUNCTION dbo.GetAccountsXml (@ownerId varchar(10))
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@xml			varchar(max)

	SET @xml		= '<ACCOUNTS><entry id="0" guid=""></entry>'

	SELECT @xml = @xml + '<entry id="' + CAST(PartnerNo AS VARCHAR) + '" guid="'+CAST(p.PartnerGUID AS VARCHAR(100))+'"><![CDATA['+ PartnerName +']]></entry>' 
	FROM TR_PARTNER as p with (NOLOCK)
	LEFT OUTER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
		on r.SourceGUID = p.PartnerGUID
	WHERE 
		(PartnerCategory = 2 AND PartnerType = 2) 
	AND (r.Partner2 = @ownerID AND r.RelationshipType = 3)
	AND isActive = 1 

	ORDER BY [PartnerName]
	set @xml = @xml + '</ACCOUNTS>'	

	return @xml
END


-- SELECT dbo.GetAccountsXml('1')
